/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Models.Drivers;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Cham to tet
 */
public class DriversDAO {

    public int addDrivers(Drivers driver) {
        int result = 0;
        try {


            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("Insert into Driver values(?,?,?,?,?,?,?,?,?)");
            stm.setString(1, driver.getName());
            stm.setString(2, driver.getContactPerson());
            stm.setString(3, driver.getAddress());
            stm.setString(4, driver.getCity());
            stm.setString(5, driver.getMobile());
            stm.setString(6, driver.getTelephone());
            stm.setString(7, driver.getExperience());
            stm.setString(8, driver.getDescription());
            stm.setNull(9, java.sql.Types.BOOLEAN);
            result = stm.executeUpdate();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public int getCurrentID() {
        int id = 0;
        try {


            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("select MAX(ID) from Driver");
            ResultSet rs = stm.executeQuery();

            while (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return id;
    }

    public static List<Drivers> getAllDriver() throws Exception {
        Connection con = (Connection) DBConnection.myConnection();
        List lst = new ArrayList();
        java.sql.Statement stm = con.createStatement();
        ResultSet rs = stm.executeQuery("Select * from Driver where Status is not null");
        while (rs.next()) {
            Drivers dr = new Drivers(rs.getInt("ID"), rs.getString("Name"), rs.getString("ContactPerson"), rs.getString("Address"), rs.getString("City"), rs.getString("Mobile"), rs.getString("Telephone"), rs.getString("Experience"), rs.getString("Description"), rs.getBoolean("Status"));
            lst.add(dr);
        }
        con.close();
        return lst;
    }

    public static List<Drivers> getDriversByKeyword(String keyword, String FindWhere) throws Exception {
        List lst = new ArrayList();
        Connection con = (Connection) DBConnection.myConnection();
        java.sql.Statement stm = con.createStatement();
        ResultSet rs = stm.executeQuery("Select * from Driver where " + FindWhere + " like '%" + keyword + "%' And Status='true'");
        while (rs.next()) {
            Drivers dr = new Drivers(rs.getInt("ID"), rs.getString("Name"), rs.getString("ContactPerson"), rs.getString("Address"), rs.getString("City"), rs.getString("Mobile"), rs.getString("Telephone"), rs.getString("Experience"), rs.getString("Description"), rs.getBoolean("Status"));
            lst.add(dr);
        }
        con.close();
        return lst;
    }

    public static List getAllCity() throws Exception {
        Connection con = (Connection) DBConnection.myConnection();
        List lst = new ArrayList();
        java.sql.Statement stm = con.createStatement();
        ResultSet rs = stm.executeQuery("Select City from Driver where Status is not null Group by City");
        while (rs.next()) {
            lst.add(rs.getString("City"));
        }
        con.close();
        return lst;
    }

    public int statisticDriver() {
        int total = 0;
        try {
            String conStr = "jdbc:sqlserver://localhost;databaseName=RadioCabsDB;user=sa;password=123456";
            Connection con = DriverManager.getConnection(conStr);
            PreparedStatement st = con.prepareStatement("select count(*) from Driver");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public int getAllDriverDisable() {
        int total = 0;
        try {
            String conStr = "jdbc:sqlserver://localhost;databaseName=RadioCabsDB;user=sa;password=123456";
            Connection con = DriverManager.getConnection(conStr);
            PreparedStatement st = con.prepareStatement("select count(*) from Company where Status = 'true' ");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public int getAllDriverEnable() {
        int total = 0;
        try {
            String conStr = "jdbc:sqlserver://localhost;databaseName=RadioCabsDB;user=sa;password=123456";
            Connection con = DriverManager.getConnection(conStr);
            PreparedStatement st = con.prepareStatement("select count(*) from Company where Status = 'true' ");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public static void deleteDriver(int id) throws Exception {
        Connection con = (Connection) DBConnection.myConnection();
        PreparedStatement st = con.prepareStatement("delete from Enquiry where DriverID=" + id);
        st.executeUpdate();
        st = con.prepareStatement("delete from Payment where DriverID=" + id);
        st.executeUpdate();
        st = con.prepareStatement("delete from Account where DriverID=" + id);
        st.executeUpdate();
        st = con.prepareStatement("delete from Driver where ID=" + id);
        st.executeUpdate();
    }

    public static void editDriver(Drivers dr) throws Exception {


        Connection con = (Connection) DBConnection.myConnection();
        String sql = "update Driver set Name =?, ContactPerson=?, [Address]=?, City=?, Mobile=?, Telephone=?, Experience=?, [Description]=?, [Status]=? where ID=?";
        PreparedStatement st = con.prepareStatement(sql);
        st.setString(1, dr.getName());
        st.setString(2, dr.getContactPerson());
        st.setString(3, dr.getAddress());
        st.setString(4, dr.getCity());
        st.setString(5, dr.getMobile());
        st.setString(6, dr.getTelephone());
        st.setString(7, dr.getExperience());
        st.setString(8, dr.getDescription());
        st.setBoolean(9, dr.isStatus());
        st.setInt(10, dr.getId());
        st.executeUpdate();
    }
}
